﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Reflection;
using System.ComponentModel;

namespace XiaoMan.DAL
{
    public class SQLHelper
    {
        /// <summary>  
        /// <add name="ConnectionString" connectionString="Data Source=192.168.18.19;Initial Catalog=clgl-ms;Persist Security Info=True;User ID=sa;Password=BBTHiermert104" providerName="System.Data.SqlClient"/> 
        /// </summary> 
        public static string conStr;
        private SqlConnection connection;

        public SQLHelper()
        {
            conStr = ConfigurationManager.ConnectionStrings["XiaoManS10Context"].ConnectionString;
            connection = new SqlConnection(conStr);
        }

        /// <summary>  
        /// 获得存储过程的Command
        /// </summary>  
        /// <param name="storedProcedure">存储过程名</param>  
        /// <returns></returns>  
        public SqlCommand GetStoredProcCommand(string storedProcedure)
        {
            SqlCommand SqlCommand = connection.CreateCommand();
            SqlCommand.CommandText = storedProcedure;
            SqlCommand.CommandType = CommandType.StoredProcedure;
            return SqlCommand;
        }

        /// <summary>  
        /// 获得SQL语句的Command
        /// </summary>  
        /// <param name="sqlQuery">SQL语句</param>  
        /// <returns></returns>  
        public SqlCommand GetSqlStringCommand(string sqlQuery)
        {
            SqlCommand SqlCommand = connection.CreateCommand();
            SqlCommand.CommandText = sqlQuery;
            SqlCommand.CommandType = CommandType.Text;
            return SqlCommand;
        }


        /// <summary>
        /// 增加参数部分
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="SqlParameterCollection"></param>
        public void AddParameterCollection(SqlCommand cmd, SqlParameterCollection SqlParameterCollection)
        {
            foreach (SqlParameter SqlParameter in SqlParameterCollection)
            {
                cmd.Parameters.Add(SqlParameter);
            }
        }

        /// <summary>  
        /// 增加输出参数  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <param name="parameterName"></param>  
        /// <param name="dbType"></param>  
        /// <param name="size"></param>  
        public void AddOutParameter(SqlCommand cmd, string parameterName, DbType dbType, int size)
        {
            SqlParameter SqlParameter = cmd.CreateParameter();
            SqlParameter.DbType = dbType;
            SqlParameter.ParameterName = parameterName;
            SqlParameter.Size = size;
            SqlParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(SqlParameter);
        }

        /// <summary>  
        /// 增加输入参数  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <param name="parameterName"></param>  
        /// <param name="dbType"></param>  
        /// <param name="value"></param>  
        public void AddInParameter(SqlCommand cmd, string parameterName, DbType dbType, object value)
        {
            SqlParameter SqlParameter = cmd.CreateParameter();
            SqlParameter.DbType = dbType;
            SqlParameter.ParameterName = parameterName;
            SqlParameter.Value = value;
            SqlParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(SqlParameter);
        }

        /// <summary>  
        /// 增加返回参数  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <param name="parameterName"></param>  
        /// <param name="dbType"></param>  
        public void AddReturnParameter(SqlCommand cmd, string parameterName, DbType dbType)
        {
            SqlParameter SqlParameter = cmd.CreateParameter();
            SqlParameter.DbType = dbType;
            SqlParameter.ParameterName = parameterName;
            SqlParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(SqlParameter);
        }

        public SqlParameter GetParameter(SqlCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }


        /*
        执行函数部分
        */

        /// <summary>  
        /// 执行查询返回DataSet  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <returns></returns>  
        public DataSet ExecuteDataSet(SqlCommand cmd)
        {
            try
            {
                SqlDataAdapter dbDataAdapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                dbDataAdapter.Fill(ds);
                return ds;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>  
        /// 执行查询返回DataTable  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <returns></returns>  
        public DataTable ExecuteDataTable(SqlCommand cmd)
        {
            try
            {
                SqlDataAdapter dbDataAdapter = new SqlDataAdapter(cmd);
                DataTable dataTable = new DataTable();
                dbDataAdapter.Fill(dataTable);
                return dataTable;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>  
        /// 执行查询返回DataReader  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <returns></returns>  
        public SqlDataReader ExecuteReader(SqlCommand cmd)
        {
            SqlDataReader reader;
            try
            {
                cmd.Connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            finally
            {
                cmd.Connection.Close();  // mxj 这个可能不需要关闭，还要测试==============
            }
            return reader;
        }

        /// <summary>  
        /// 执行SQL语句，返回影响行数  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <returns></returns>  
        public int ExecuteNonQuery(SqlCommand cmd)
        {
            int ret;
            try
            {
                cmd.Connection.Open();
                ret = cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Connection.Close();
            }

            return ret;
        }

        /// <summary>  
        /// 返回首行首列对象  
        /// </summary>  
        /// <param name="cmd"></param>  
        /// <returns></returns>  
        public object ExecuteScalar(SqlCommand cmd)
        {
            object ret;
            try
            {
                cmd.Connection.Open();
                ret = cmd.ExecuteScalar();
            }
            finally
            {
                cmd.Connection.Close();
            }
            return ret;
        }


        /// <summary>
        /// *****************************************************
        /// ***********   mxj  主要使用的函数：  ****************
        /// 执行SQL语句，返回影响行数，添加、修改、删除使用本函数
        /// *****************************************************
        /// *****************************************************
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecSql(string sql)
        {
            return ExecuteNonQuery(GetSqlStringCommand(sql));
        }

        /// <summary>
        /// 执行SQL语句，返回DataSet，查询使用本函数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExecSqlForDataSet(string sql)
        {
            return ExecuteDataSet(GetSqlStringCommand(sql));
        }

        /// <summary>
        /// 执行SQL语句，返回DataTable，查询使用本函数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecSqlForDataTable(string sql)
        {
            return ExecuteDataTable(GetSqlStringCommand(sql));
        }


        /// <summary>
        /// DataTable转List:根据指定类型，从数据库中获取数据，存放到List<T>集合中
        /// </summary>
        /// <typeparam name="T">泛型对象</typeparam>
        /// <param name="_classType">类类型，即数据库中的表名</param>
        /// <param name="_sql">查询表的SQL</param>
        /// <param name="_cmdParams">SQL参数</param>
        /// <returns></returns>
        public List<T> MakeTablePackage<T>(string _sql,            //where T:class,new() 表示类型T必须是类，并且可以实例化
                   params SqlParameter[] _cmdParams) where T : class, new()
        {
            try
            {
                List<T> _lstReturn = new List<T>();
                DataTable _dtGet = ExecSqlForDataTable(_sql);


                //获得属性集合
                T _tmpObj = new T();
                Type _type = _tmpObj.GetType();
                PropertyInfo[] _properties = _type.GetProperties();


                for (int i = 0; i < _dtGet.Rows.Count; i++)
                {
                    T _item = new T();
                    foreach (PropertyInfo _property in _properties)
                    {
                        if (_dtGet.Columns.Contains(_property.Name))  // mxj 2017.8.19 增加对表列的判断，这样如果类里面定义的属性比表格列多的话，也没有问题
                        {
                            object _value = _dtGet.Rows[i][_property.Name].ToString();
                            _property.SetValue(_item,
                                _value.ChangeType(_property.PropertyType)
                                , null);
                        }
                    }
                    _lstReturn.Add(_item);
                }
                return _lstReturn;
            }
            catch (Exception)
            {
                throw new Exception("打包数据出错-MakeTablePackage");
            }
        }
    }
    ///DataTable转List辅助类
    public static class DBConvert
    {
        public static object ChangeType(this object value, Type conversionType)          //第一个参数加this,表示该ChangeType方法将为object的扩展方法
        {
            if (conversionType.IsGenericType &&
                    conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value != null)
                {
                    NullableConverter nullableConverter = new NullableConverter(conversionType);
                    conversionType = nullableConverter.UnderlyingType;
                }
                else
                {
                    return null;
                }
            }
            if (conversionType.Equals(typeof(System.DateTime)) && (value.Equals("")))
            {
                return null;
            }
            else
            {
                return Convert.ChangeType(value, conversionType);
            }
        }
    }
    
    /// <summary>  
    /// 数据表字段类  
    /// </summary>  
    public class FieldTypeValue
    {
        /// <summary>  
        /// 字段容器  
        /// </summary>  
        /// <param name="fieldName">字段名</param>  
        /// <param name="fieldValue">字段值</param>  
        /// <param name="isNum">是否数字字段</param>  
        public FieldTypeValue(string fieldName, string fieldValue, bool isNum)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
            this.isNum = isNum;
        }

        /// <summary>  
        /// 字段容器  
        /// </summary>  
        /// <param name="fieldName">字段名</param>  
        /// <param name="fieldValue">字段值</param>  
        public FieldTypeValue(string fieldName, string fieldValue)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
        }

        private string fieldName;
        /// <summary>  
        /// 字段名  
        /// </summary>  
        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }

        private bool isNum = false;
        /// <summary>  
        /// 是否数字  
        /// </summary>  
        public bool IsNum
        {
            get { return isNum; }
            set { isNum = value; }
        }

        private string fieldValue;
        /// <summary>  
        /// 字段值  
        /// </summary>  
        public string FieldValue
        {
            get { return fieldValue; }
            set { fieldValue = value; }
        }
    }



    /// <summary>  
    /// SQL语句的构造类  
    /// </summary>  
    public class BuilderSql
    {

        /// <summary>  
        /// 构造新增Insert语句  
        /// </summary>  
        /// <param name="tableName">表名</param>  
        /// <param name="ftvlist">字段list</param>  
        /// <returns></returns>  
        public static string createInsertSql(string tableName, List<FieldTypeValue> ftvlist)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" insert into ");
            sb.Append(tableName);
            sb.Append("(");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    sb.Append(ftv.FieldName + ",");
                }
                else
                {
                    sb.Append(ftv.FieldName);
                }
            }
            sb.Append(") values(");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (ftv.IsNum)
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append(ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(ftv.FieldValue);
                    }
                }
                else
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append("'" + ftv.FieldValue + "',");
                    }
                    else
                    {
                        sb.Append("'" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(")");
            return sb.ToString();
        }


        /// <summary>  
        /// 构造更新Update语句  
        /// </summary>  
        /// <param name="tableName">表名</param>  
        /// <param name="ftvlist">字段list</param>  
        /// <param name="pkName">主键名</param>  
        /// <param name="pkValue">主键值</param>  
        /// <returns></returns>  
        public static string createUpdateSql(string tableName, List<FieldTypeValue> ftvlist, string pkName, string pkValue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" update ");
            sb.Append(tableName);
            sb.Append(" set");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',");
                    }
                }
                else
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + "");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(" where " + pkName + "= '" + pkValue + "'");
            return sb.ToString();
        }

        /// <summary>  
        /// 构造删除Delete语句  
        /// </summary>  
        /// <param name="tableName">表名</param>  
        /// <param name="pkName">主键名</param>  
        /// <param name="pkValue">主键值</param>  
        /// <returns></returns>  
        public static string createDeleteSql(string tableName, string pkName, string pkValue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" delete from ");
            sb.Append(tableName);
            sb.Append(" where " + pkName + " = '" + pkValue + "'");
            return sb.ToString();
        }
    }
}
